"""数据库连接模块

负责数据库连接和基础查询功能。
"""

from typing import List, Dict, Any
from sqlalchemy import create_engine, text
from sqlalchemy.engine import Engine
from tenacity import retry, wait_fixed, stop_after_attempt
import logging
import random
from datetime import datetime
from config.config_manager import get_database_config, is_mock_mode

# 日志
logger = logging.getLogger(__name__)

# 数据库引擎缓存
_engine_cache: Dict[str, Engine] = {}

# 模拟数据 - 适配 HIS 数据库字段结构
MOCK_DRUG_DATA = [
    {
        "idm": "D001",
        "ypmc": "阿莫西林胶囊",
        "ypgg": "0.25g*24粒",
        "ykxs": 1,
        "jhdw": "盒",
        "zxdw": "粒",
        "cjdm": "M001",
        "cjmc": "华北制药",
        "ypfj": 15.50,
        "pzwh": "国药准字H12345678"
    },
    {
        "idm": "D002",
        "ypmc": "布洛芬缓释胶囊",
        "ypgg": "0.3g*20粒",
        "ykxs": 1,
        "jhdw": "盒",
        "zxdw": "粒",
        "cjdm": "M002",
        "cjmc": "中美史克",
        "ypfj": 28.80,
        "pzwh": "国药准字H87654321"
    },
    {
        "idm": "D003",
        "ypmc": "复方甘草片",
        "ypgg": "100片",
        "ykxs": 1,
        "jhdw": "瓶",
        "zxdw": "片",
        "cjdm": "M003",
        "cjmc": "太极集团",
        "ypfj": 8.90,
        "pzwh": "国药准字Z11111111"
    },
    {
        "idm": "D004",
        "ypmc": "维生素C片",
        "ypgg": "0.1g*100片",
        "ykxs": 1,
        "jhdw": "瓶",
        "zxdw": "片",
        "cjdm": "M004",
        "cjmc": "石药集团",
        "ypfj": 12.30,
        "pzwh": "国药准字H22222222"
    },
    {
        "idm": "D005",
        "ypmc": "感冒清热颗粒",
        "ypgg": "12g*10袋",
        "ykxs": 1,
        "jhdw": "盒",
        "zxdw": "袋",
        "cjdm": "M005",
        "cjmc": "同仁堂",
        "ypfj": 35.60,
        "pzwh": "国药准字Z33333333"
    }
]


def get_engine(dsn: str = None) -> Engine:
    """获取数据库引擎

    Args:
        dsn: 数据库连接字符串，如果为None则使用默认配置

    Returns:
        Engine: SQLAlchemy 引擎
    """
    # 使用默认连接字符串
    if dsn is None:
        db_config = get_database_config()

        dsn = f"mssql+pyodbc://{db_config['user']}:{db_config['password']}@{db_config['host']}/{db_config['database']}?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=yes"#@朱文志
        #dsn = f"mssql+pyodbc://{db_config['user']}:{db_config['password']}@{db_config['host']}/{db_config['database']}?ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=yes"

    if dsn in _engine_cache:
        return _engine_cache[dsn]

    try:
        # 获取数据库配置
        db_config = get_database_config()

        # 添加连接超时和重试配置
        engine = create_engine(
            dsn,
            pool_pre_ping=db_config["pool_pre_ping"],
            pool_recycle=db_config["pool_recycle"],
            future=True,
            connect_args={
                "connect_timeout": db_config["connection_timeout"],
                "autocommit": True
            }
        )
        _engine_cache[dsn] = engine
        logger.info(f"数据库连接已建立: {db_config['host']}:{db_config['database']}")
        return engine
    except Exception as e:
        logger.error(f"数据库连接失败: {str(e)}")
        logger.error("请检查：1. SQL Server ODBC 驱动是否已安装 2. 网络连接是否正常 3. 数据库服务是否运行")
        raise


@retry(wait=wait_fixed(1), stop=stop_after_attempt(3))
def query_dicts(engine: Engine, sql: str) -> List[Dict[str, Any]]:
    """执行 SQL 查询并返回字典列表

    Args:
        engine: SQLAlchemy 引擎
        sql: SQL 查询语句

    Returns:
        List[Dict[str, Any]]: 查询结果字典列表
    """
    # 如果是模拟模式，返回模拟数据
    if is_mock_mode():
        logger.info("模拟模式：返回模拟药品字典数据")
        # 模拟一些随机变化
        mock_data = []
        for item in MOCK_DRUG_DATA:
            mock_item = item.copy()
            # 随机调整价格
            mock_item["ypfj"] = round(item["ypfj"] * (0.95 + random.random() * 0.1), 2)
            mock_data.append(mock_item)

        logger.info(f"模拟查询执行成功，返回 {len(mock_data)} 条记录")
        return mock_data

    try:
        with engine.connect() as conn:
            result = conn.execute(text(sql))
            rows = result.mappings().all()
            data = [dict(r) for r in rows]
            logger.info(f"查询执行成功，返回 {len(data)} 条记录")
            return data
    except Exception as e:
        logger.error(f"数据库查询失败: {str(e)}")
        raise


def test_connection(dsn: str) -> bool:
    """测试数据库连接

    Args:
        dsn: 数据库连接字符串

    Returns:
        bool: 连接是否成功
    """
    # 如果是模拟模式，直接返回成功
    if HOSPITAL_DB_CONFIG.get("mock_mode", False):
        logger.info("模拟模式：数据库连接测试成功")
        return True

    try:
        engine = get_engine(dsn)
        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
        logger.info("数据库连接测试成功")
        return True
    except Exception as e:
        logger.error(f"数据库连接测试失败: {str(e)}")
        return False


__all__ = ["get_engine", "query_dicts", "test_connection"]
